# coding: utf-8

# In[1]:


import xlwings as xw
#import numpy as np
import pandas as pd
#import time
#import re
import os


# In[6]:


path = r'D:\文档\表格\工资数据\好兄妹\河南好兄妹工资\工资'
#path
os.listdir(path)
path_lst = [os.path.join(path,i) for i in os.listdir(path)]


# In[102]:


app = xw.App(visible=True, add_book=False)
app.display_alerts = False
app.screen_updating = True


# In[103]:


x = path_lst[11]
#x


# In[104]:


xb = app.books.open(x)
sheet_name = [i.name for i in list(xb.sheets) if '月工资' in i.name][0]
xb.sheets[sheet_name].activate()
#sheet_name


# In[105]:


B_col = xb.sheets[sheet_name].range('b1:b1000').value
a1 = B_col.index('管理')
a2 = B_col.index('后勤')
a3 = B_col.index('裁剪')
a4 = B_col.index('1组')
a5 = B_col.index('锁定')
a6 = B_col.index('大烫')
a7 = B_col.index('光检')
a8 = B_col.index('剪线毛')
a9 = B_col.index('包装')


# In[106]:


chegong = chegong = xb.sheets[sheet_name].range('a'+str(a4+2)+':o'+str(a5-1)).options(pd.DataFrame).value
chegong = chegong[True^chegong['出勤天数'].isin([None])]
chegong.drop('人数', inplace=True)
chegong = chegong[(chegong['出勤天数'] >= 20.0) & (chegong['出勤天数'] < 40.0)]
# chegong.head()
renshu_chegong = chegong['姓名'].count()
aver_pay_cg = chegong['实发工资'].sum()/renshu_chegong
aver_date_cg = chegong['出勤天数'].sum()/renshu_chegong
#renshu_chegong, aver_date_cg, aver_pay_cg


# In[107]:


caijian = xb.sheets[sheet_name].range('a'+str(a3+2)+':o'+str(a4-1)).options(pd.DataFrame).value
caijian = caijian[(caijian['出勤天数'] >= 20.0) & (caijian['出勤天数'] < 40.0)]
renshu_caijian = caijian['姓名'].count()
aver_pay = caijian['实发工资'].sum()/renshu_caijian
aver_date = caijian['出勤天数'].sum()/renshu_caijian
#renshu_caijian, aver_date, aver_pay


# In[108]:


zhengtang = xb.sheets[sheet_name].range('a'+str(a6+2)+':o'+str(a7-1)).options(pd.DataFrame).value
zhengtang = zhengtang[(zhengtang['出勤天数'] >= 15.0) & (zhengtang['出勤天数'] < 40.0)]
# zhengtang
renshu_zhengtang = zhengtang['姓名'].count()
aver_pay_zt = zhengtang['实发工资'].sum()/renshu_zhengtang
aver_date_zt = zhengtang['出勤天数'].sum()/renshu_zhengtang
#renshu_zhengtang, aver_date_zt, aver_pay_zt


# In[109]:


jiancha = xb.sheets[sheet_name].range('a'+str(a7+2)+':o'+str(a8-1)).options(pd.DataFrame).value
jiancha = jiancha[(jiancha['出勤天数'] >= 20.0) & (jiancha['出勤天数'] < 40.0)]
# jiancha
renshu_jiancha = jiancha['姓名'].count()
aver_pay_jc = jiancha['实发工资'].sum()/renshu_jiancha
aver_date_jc = jiancha['出勤天数'].sum()/renshu_jiancha
#renshu_jiancha, aver_date_jc, aver_pay_jc


# In[110]:


baozhuang = xb.sheets[sheet_name].range('a'+str(a9+2)+':o'+str(a9+100)).options(pd.DataFrame).value
baozhuang = baozhuang[(baozhuang['出勤天数'] >= 20.0) & (baozhuang['出勤天数'] < 40.0)]
# baozhuang
renshu_baozhuang = baozhuang['姓名'].count()
aver_pay_bz = baozhuang['实发工资'].sum()/renshu_baozhuang
aver_date_bz = baozhuang['出勤天数'].sum()/renshu_baozhuang
#renshu_baozhuang, aver_date_bz, aver_pay_bz
# baozhuang


# In[111]:


suoding = xb.sheets[sheet_name].range('a'+str(a5+2)+':o'+str(a6-1)).options(pd.DataFrame).value
suoding = suoding[(suoding['出勤天数'] >= 20.0) & (suoding['出勤天数'] < 40.0)]
# suoding
renshu_suoding = suoding['姓名'].count()
aver_pay_sd = suoding['实发工资'].sum()/renshu_suoding
aver_date_sd = suoding['出勤天数'].sum()/renshu_suoding
#renshu_suoding, aver_date_sd, aver_pay_sd


# In[112]:


# jianxianmao = xb.sheets[sheet_name].range('a'+str(a8+2)+':o'+str(a9-1)).options(pd.DataFrame).value
# jianxianmao = jianxianmao[(jianxianmao['出勤天数'] >= 20.0) & (jianxianmao['出勤天数'] < 40.0)]
# jianxianmao


# In[114]:


xb.close()
print(x)
print('\n',renshu_chegong, aver_date_cg, aver_pay_cg,'\n'
     '\n', renshu_caijian, aver_date, aver_pay, '\n'
     '\n', renshu_zhengtang, aver_date_zt, aver_pay_zt,'\n'
     '\n', renshu_baozhuang, aver_date_bz, aver_pay_bz, '\n'
     '\n', renshu_jiancha, aver_date_jc, aver_pay_jc, '\n'
     '\n', renshu_suoding, aver_date_sd, aver_pay_sd)

app.quit()
